import io
import os

import pandas as pd

from openpyxl import load_workbook
from ..config import Config
from ..services.pinxiang import pinxiang_exe


class PinxiangServices:
    colMap = {
        "品牌名称": 1,
        "大系列": 2,
        "子系列": 3,
        "此店铺当前发货开单价": 4,
        "此店铺当前销售": 5,
        "此店铺当前销售同比": 6,
        "此店铺当前折扣": 7,
        "此店铺当前销售占比": 8,
        "此店铺当前占比涨跌同比": 9,
        "此店铺累计发货开单价": 10,
        "此店铺累计销售": 11,
        "此店铺累计销售同比": 12,
        "此店铺累计折扣": 13,
        "此店铺累计销售占比": 14,
        "此店铺累计占比涨跌同比": 15,
        "全店铺当前发货开单价": 17,
        "全店铺当前销售": 18,
        "全店铺当前销售同比": 19,
        "全店铺当前折扣": 20,
        "全店铺当前销售占比": 21,
        "全店铺当前占比涨跌同比": 22,
        "全店铺累计发货开单价": 23,
        "全店铺累计销售": 24,
        "全店铺累计销售同比": 25,
        "全店铺累计折扣": 26,
        "全店铺累计销售占比": 27,
        "全店铺累计占比涨跌同比": 28
    }

    @staticmethod
    def exportXlsx(sd: str, ed: str, tsn: str):
        template_path = Config.PINXIANG_XLSX_TEMPLATE_FILE
        # 加载模板文件
        wb = load_workbook(template_path)

        # 获取模板文件中的第一个工作表
        sheet = wb.worksheets[0]
        dataframe = pinxiang_exe(sd, ed, tsn)
        # 将数据写入模板的指定位置
        for index, row in dataframe.iterrows():
            for column in dataframe.columns:
                colunm_index = PinxiangServices.colMap.get(column)
                if colunm_index is not None:
                    df_value = row[column]
                    sheet.cell(row=6+index, column=colunm_index).value = df_value
        # 保存为 BytesIO 对象
        file_data = io.BytesIO()
        wb.save(file_data)
        file_data.seek(0)

        return file_data
